#!user/bin/python
#coding: utf-8

import MySQLdb
import sqlite3
from datetime import datetime

class DAO:
    host = u"127.0.0.1"
    user = u"root"
    passwd = u"123456"
    db = u"waterinfo"
    charset = u"utf8"
    sqlitedb = u"mekong.db"
    conn = None

    def __init__(self,dbtype):
        ''''''
        if dbtype == u'mysql':
            try:
                self.conn = MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
            except Exception, e:
                print e
        if dbtype == u'sqlite':
            try:
                self.conn = sqlite3.connect(self.sqlitedb)
            except Exception, e:
                print e

    def __del__(self):
        self.conn.close()

    def insert(self,minfo):
        ''''''
        cursor = self.conn.cursor()
        sql = "INSERT INTO news(title,href,source,publish_date,record_date) VALUES('%s','%s','%s','%s','%s')" % \
              (minfo.title, minfo.href, minfo.source, minfo.publish_date, minfo.record_date)
        try:
            cursor.execute(sql)
        except Exception, e:
            print e
        cursor.close()
        self.conn.commit()

    def query_all(self):
        cursor = self.conn.cursor()
        sql = u'SELECT * FROM news ORDER BY id'
        try:
            cursor.execute(sql)
            alldata=cursor.fetchall()
            return alldata or ['']
        except Exception, e:
            print e
        cursor.close()

    def query_not_download(self):
        cursor = self.conn.cursor()
        sql = u'SELECT * FROM news WHERE isdown=0 ORDER BY id'
        try:
            cursor.execute(sql)
            alldata=cursor.fetchall()
            return alldata or ['']
        except Exception, e:
            print e
        cursor.close()

    def update_text(self,minfo):
        cursor = self.conn.cursor()
        current_time=str(datetime.now())
        sql = u"UPDATE news SET text='%s',isdown=1,modify_time='%s' WHERE id=%d" % \
              (minfo.text,current_time,minfo.id)
        try:
            cursor.execute(sql)
        except Exception, e:
            print e
        cursor.close()
        self.conn.commit()